/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package uit.tkorg.coauthorgraph.dbaccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import uit.tkorg.coauthorgraph.dto.ConferenceDTO;

/**
 *
 * @author Huynh Ngoc Tin
 */
public class ConferenceMapper extends MapperDB {
    public ConferenceMapper() throws Exception {
        super();
    }

    public ConferenceMapper(Connection con) {
        super(con);
    }

    /**
     * check if the specified conference exist in the DB
     * @param paperDTO
     * @return
     * @throws Exception
     */
    public boolean isConferenceExisted(ConferenceDTO confDTO) throws Exception {
        boolean isExist = false;
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT * FROM coauthorgraph.conference conf");
            sql.append(" WHERE conf.name = ? AND conf.year = ?");
            Connection con = this.getConnection();
            if (con != null) {
                PreparedStatement stmt = con.prepareStatement(sql.toString());
                stmt.setString(1, confDTO.getConferenceName());
                stmt.setInt(2, confDTO.getYear());
                ResultSet rs = stmt.executeQuery();
                if ((rs != null) && (rs.next())) {
                    isExist = true;
                }
                stmt.close();
            }
        }
        catch (Exception e) {
            System.out.println(e.toString());
            throw e;
        }

        return isExist;
    }

    /**
     * get the conference ID from confName and year
     * @param confName
     * @return
     * @throws Exception
     */
    public int getConferenceID(String confName, int year) throws Exception {
        int confID = -1;
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT conferenceID FROM coauthorgraph.conference conf");
            sql.append(" WHERE conf.conferenceName = ? AND conf.year = ?");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.setString(1, confName);
            stmt.setInt(2, year);
            ResultSet rs = stmt.executeQuery();
            if ((rs != null) && (rs.next())) {
                confID = rs.getInt("conferenceID");
            }
            stmt.close();
        }
        catch (Exception e) {
            System.out.println(e.toString());
            throw e;
        }

        return confID;
    }

    /**
     * insert a new conference into the DB
     * @param authorDTO
     * @throws Exception
     */
    public int insertNewConference(ConferenceDTO confDTO) throws Exception {
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("INSERT INTO coauthorgraph.conference(conferenceName, organization, organizedLocaion, year) ");
            sql.append(" VALUES(?, ?, ?, ?)");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.setString(1, confDTO.getConferenceName());
            stmt.setString(2, confDTO.getOrganization());
            stmt.setString(3, confDTO.getOrganizedLocation());
            stmt.setInt(4, confDTO.getYear());
            stmt.execute();

            // get the last inserted ID
            ResultSet rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");
            int insertConfID = -1;
            if (rs != null && rs.next())
                insertConfID = rs.getInt(1);

            stmt.close();
            return insertConfID;
        }
        catch (Exception e) {
            System.out.println(e.toString());
            throw e;
        }
    }

    /**
     * getConferenceNameListForAuthorList
     * @param authorIDList
     * @return
     * @throws Exception
     */
    public ArrayList getConferenceNameListForAuthorList(ArrayList authorIDList) throws Exception {
        ArrayList confNameList = new ArrayList();
        StringBuffer sqlStr1 = new StringBuffer();
        sqlStr1.append(" SELECT c.conferenceID, c.conferenceName");
        sqlStr1.append(" FROM coauthorgraph.conference c, coauthorgraph.paper p");
        sqlStr1.append(" WHERE p.conferenceID = c.conferenceID AND p.paperID IN ");
        sqlStr1.append(" (SELECT ap.paperID FROM coauthorgraph.authorpaper ap WHERE ap.authorID IN (");
        for (int i=0; i<authorIDList.size(); i++) {
            String curAuthorID = (String) authorIDList.get(i);
            if (i == authorIDList.size()-1)
                sqlStr1.append(curAuthorID);
            else
                sqlStr1.append(curAuthorID + ",");
        }
        sqlStr1.append(" ))");
        sqlStr1.append(" GROUP BY c.conferenceName");
        Connection conn = getConnection();
        PreparedStatement stmt1 = conn.prepareStatement(sqlStr1.toString());
        ResultSet rs1 = stmt1.executeQuery();

        while (rs1 != null && rs1.next()) {
            String confName = rs1.getString("conferenceName");
            confNameList.add(confName);
        }

        rs1.close();
        stmt1.close();
        return confNameList;
    }
}
